﻿using MySql.Data.MySqlClient;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Senparc.Weixin.MP.AdvancedAPIs.OAuth;
using NB.MGJ.Common;
using Common;

namespace NB.MGJ.Service.Market
{
    //注意，这里的骚操作直接是 库名.表名,万一以后如果是把库分服务器了，就不能这样写了
    public class TheWinningService
    {
        public BaseRepository _baseRepository = new BaseRepository();
        DBHService _dbhservice = new DBHService();
        //MarketingUserService MarUser = new MarketingUserService();

 
        #region 新增
        /// <summary>
        /// 添加方法（通过传递实体类添加数据，把添加的数据赋值给实体类属性，不添加的值不填写）
        /// </summary>
        /// <param name="ScanCodeInfoModel">ScanCodeInfoModel实体类</param>
        /// <returns>int 0为失败 ；1为成功</returns>
        public int Insert(Marketing_TheWinning model, string tableName)
        {
          
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            int pcount = 0;
            //int i = 0;
            if (model.ActivityID != null)
            {
                strSql1.Append("ActivityID,");
                strSql2.Append("@ActivityID ,");
                pcount += 1;
            }
            if (model.UserID != null)
            {
                strSql1.Append("UserID,");
                strSql2.Append("@UserID ,");
                pcount += 1;
            }
            if (model.AwardsID != null)
            {
                strSql1.Append("AwardsID,");
                strSql2.Append("@AwardsID ,");
                pcount += 1;
            }
            if (model.Time != null)
            {
                strSql1.Append("Time,");
                strSql2.Append("@Time ,");
                pcount += 1;
            }
            if (model.Groupid != null)
            {
                strSql1.Append("GroupID,");
                strSql2.Append("@GroupID ,");
                pcount += 1;
            }
            if (model.JiaMiCode!=null)
            {
                   strSql1.Append("JiaMiCode,");
                strSql2.Append("@JiaMiCode ,");
                pcount += 1;
            }
            if (model.Code != null)
            {
                strSql1.Append("code,");
                strSql2.Append("@code ,");
                pcount += 1;
            }
            if (model.BatchID != null)
            {
                strSql1.Append("BatchID,");
                strSql2.Append("@BatchID ,");
                pcount += 1;
            }
            if (model.Denomination != null)
            {
                strSql1.Append("Denomination,");
                strSql2.Append("@Denomination ,");
                pcount += 1;
            }
            if (model.State != null)
            {
                strSql1.Append("State,");
                strSql2.Append("@State ,");
                pcount += 1;
            }
            if (model.Phone != null)
            {
                strSql1.Append("Phone,");
                strSql2.Append("@Phone ,");
                pcount += 1;
            }
            if (model.Address != null)
            {
                strSql1.Append("Address,");
                strSql2.Append("@Address ,");
                pcount += 1;
            }
            if (model.Remark != null)
            {
                strSql1.Append("Remark,");
                strSql2.Append("@Remark ,");
                pcount += 1;
            }
            MySqlParameter[] parameters = 
                {
					new MySqlParameter("@ActivityID", model.ActivityID),
					new MySqlParameter("@UserID", model.UserID),
					new MySqlParameter("@AwardsID", model.AwardsID),
					new MySqlParameter("@Time", model.Time),
					new MySqlParameter("@GroupID", model.Groupid),
                    new MySqlParameter("@JiaMiCode", model.JiaMiCode),
					new MySqlParameter("@code", model.Code),
					new MySqlParameter("@BatchID", model.BatchID),
                    new MySqlParameter("@Denomination", model.Denomination),
                    new MySqlParameter("@State", model.State),
                    new MySqlParameter("@Phone", model.Phone),
                    new MySqlParameter("@Address", model.Address) ,
                    new MySqlParameter("@Remark", model.Remark) ,
                 };
            strSql.Append("insert into " + "marketing." + tableName + "(");
            strSql.Append(strSql1.ToString().TrimEnd(','));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().TrimEnd(','));
            strSql.Append(")");
             WeiXinHelper.WriteLog("GetAwards-inser",strSql.ToString());
             WeiXinHelper.WriteLog("GetAwards-inser参数", string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}"
                , model.ActivityID, model.UserID, model.AwardsID, model.Time, model.Code, model.Groupid, model.BatchID, model.Denomination, model.State));
            return _baseRepository.ExecuteSql<int>(strSql.ToString(), parameters);

        }

        #endregion

        #region 修改
        /// <summary>
        /// 添加中奖记录手机号记录 和改变状态为2
        /// </summary>
        /// <param name="eCode"></param>
        /// <param name="phone"></param>
        /// <param name="tId"></param>
        /// <param name="uId"></param>
        /// <returns></returns>
        public int FillPhone(string eCode, string phone, int tId, int uId)
        {
            string updThewinningSql = " update marketing.marketing_thewinning" + eCode + " SET State = 2,"
                                          + " Phone ='" + phone + "',time=NOW() WHERE "
                                          + @" id =" + tId + "  ";
            string updUserSql = " UPDATE marketing.marketing_user" + eCode + " SET Phone ='" + phone + "' WHERE id=" + uId;
            string sql = updThewinningSql + ";" + updUserSql;

            return _baseRepository.ExecuteSql<int>(sql);

        }


        /// <summary>
        /// 添加地址 修改中奖金额  状态
        /// </summary>
        /// <param name="model"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int UpdateTheWinning(Marketing_TheWinning model, string code)
        {
                 string sql="update marketing.Marketing_TheWinning{0} set address='{1}',state={2},Denomination={3},AwardsId={4} where id={5}";
                   sql=string.Format(sql,code,model.Address,model.State,model.Denomination,model.AwardsID,model.Id);
          return _baseRepository.ExecuteSql<int>(sql );
        }

        public int UpdateTheWinningState(int tId, int state, string code)
        {
            string sql = "update marketing.Marketing_TheWinning{0} set  state={1}  where id={2}";
            sql = string.Format(sql, code, state, tId);
            return _baseRepository.ExecuteSql<int>(sql);
        }

        #endregion

        #region 获取单个Model
        public Marketing_TheWinning GetTheWinningModel(string ecode,string code)
        {
            string sql = string.Format("select * from marketing.marketing_thewinning{0} where code={1}", ecode, code);

            return _baseRepository.ExecuteText<Marketing_TheWinning>(sql);
        }
        #endregion

        #region 创建表
        /// <summary>
        /// 表名 创建领取奖项信息表
        /// </summary>
        /// <param name="tableName"></param>
        public void CreateTheWinningTable(string tableName)
        {
            try
            {
                string createSql = string.Format(@"
                   use  marketing;
                   DROP TABLE IF EXISTS `{0}`;
                     CREATE TABLE `{1}` (
                     `ID` int(11) not NULL  primary key auto_increment,
                     `ActivityID` int not NULL, 
                     `UserID` int not NULL,
                     `AwardsID` int(11) ,
                     `Time` datetime not NULL,
                     `GroupID` int(11) DEFAULT NULL,
                     `JiaMiCode` varchar(50)  ,
                     `Code` varchar(50) not NULL,
                     `BatchID` int  ,
                     `Denomination` decimal(10,2),
                     `State` int(2),
                     `Phone` varchar(255),
                     `Address` varchar(255),
                     `Remark` varchar(255)
                   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", tableName, tableName);
                WeiXinHelper.WriteLog("创建记录表SQL", createSql);
                _baseRepository.ExecuteSql<int>(createSql);
            }
            catch (Exception ex)
            {
                WeiXinHelper.WriteLog("创建中奖记录err", ex.ToString());
                throw;
            }
        }


        #endregion

        #region 根据OpenID 查询
        /// <summary>
        /// 根据OpenID 查询
        /// </summary>
        /// <param name="OpenID"></param>
        /// <returns>用户ID</returns>
        public int GetUserID(string OpenID, string tableName)
        {
            string sql = "select ID from marketing." + tableName + " where  OpenID='" + OpenID + "'";
            return int.Parse(_baseRepository.GetSingle(sql).ToString());
        }
        #endregion

        #region 数据是否存在
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"> id</param>
        /// <param name="code">查询的表</param>
        /// <returns></returns>
        public bool IsDataRow(string tableName, string openid)
        {

            string sql = "select count(*) from marketing." + tableName + " where OpenID='" + openid + "'";

            int count = Convert.ToInt32(_baseRepository.GetSingle(sql));
            if (count > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 获取当前活动count

        /// <summary>
        /// 获取当前活动人数
        /// </summary>
        /// <returns></returns>
        public int GetThewinningCount(int Aid,string code) 
        {
            string sql = "select count(*) from marketing.marketing_thewinning" + code + " where  ActivityID=" + Aid + " GROUP by UserID";
           return   _baseRepository.GetDataTable(sql).Rows.Count;
        }
        #endregion


    }
}
